<?php

class select_builder
{
  /*
   * My private variable
   */
  private $selectfields = array();
  private $constraints  = array();
  private $tables       = array();
  private $tablelist    = array();

  private $groupby      = array();
  private $orderby      = array();
  private $having       = array();
  private $start        = 0;
  private $limit        = 0;

  private $selectstring = "";
  private $tablestring  = "";
  private $wherestring  = "";
  private $groupstring  = "";
  private $orderstring  = "";
  private $limitstring  = "";
  private $havingstring = "";

  private $sqlstatement = "";

  private $or_constraints = array();
  private $or_mode = false;

  /*
   * Setters for selection
   */
  public function set_select($field, $distinct = false)
  {
    // resets the select and sets it with new fields
    $this->selectfields = array();
    $this->add_select($field, $distinct);
  }
  public function add_select($field, $distinct = false)
  {
    if (count($this->selectfields) > 0 && $distinct)
    {
      echo "Can't add 'DISTINCT' to secondary select columns, only to first"; exit;
    }
    $distinct = ($distinct == false) ? ''                    : 'DISTINCT';
    $field    = is_array($field)     ? implode(',',$field)   : $field;

    $this->selectfields[] = "$distinct $field";
  }
  public function add_sum_select($field, $alias = null, $distinct = false)
  {
    $alias    = ($alias == null)     ? $alias = "sum_$field" : $alias;
    $distinct = ($distinct == false) ? ''                    : 'DISTINCT';

    $this->selectfields[] = "SUM($distinct $field ) $alias";
  }

  public function add_floor_select($field, $denominator, $alias = null)
  {
    $alias    = ($alias == null)     ? $alias = "floor_$field" : $alias;
    $this->selectfields[] = "FLOOR($field / $denominator) $alias";
  }

  public function add_count_select($field, $alias = null, $distinct = false)
  {
    $alias    = ($alias == null)     ? $alias = "cnt_$field" : $alias;
    $distinct = ($distinct == false) ? ''                    : 'DISTINCT';

    $this->selectfields[] = "COUNT($distinct $field ) $alias";
  }

  /*
   * Setters for tables
   */
  public function add_table($tablename)
  {
    if (!in_array($tablename, $this->tablelist))  // to prevent double tables
    {
      $this->tables[]    = $tablename;
      $this->tablelist[] = $tablename;
    }
  }
  public function add_join($tablename, $constraint, $direction = null)
  {
    if (count($this->tables) == 0)
    {
      echo "Can't add 'JOIN $tablename ON $constraint' as primary table: use addTable first!"; exit;
    }
    $dependancy = ($direction == 'left')  ? 'LEFT'  : $direction;
    $dependancy = ($direction == 'right') ? 'RIGHT' : $dependancy;

    if (!in_array($tablename, $this->tablelist))  // to prevent double tables
    {
      $this->tables[] = "$dependancy JOIN $tablename ON $constraint";
      $this->tablelist[] = $tablename;
    }
  }

  /*
   * Setters for constraints
   */
  public function add_relation($field, $value)
  {
    $this->add_constraint($field,'=', $value);
  }
  public function add_constraint($field, $operator, $value)
  {
    if (!empty($field))
    {
      if($this->or_mode === false)
      {
        $this->constraints[] = "$field $operator $value";
      }
      else
      {
        $this->or_constraints[] = "$field $operator $value";
      }
    }

  }
  public function add_in_constraint($field, $value, $negation = false)
  {
    $valuestring = is_array($value) ? implode(",",$value) : $value;

    $negate = $negation ? "NOT" : "";

    if($this->or_mode === false)
    {
      $this->constraints[] = "$field $negate IN ($valuestring)";
    }
    else
    {
      $this->or_constraints[] = "$field $negate IN ($valuestring)";
    }
  }

  /**
   * @param $field string
   * @param $arr_placeholders array array of placeholders
   * @param $arr_values array values which will be sent to query
   * @param $negation
   *
   * Create prepare data to sent into query and create constraint
   **/
  public function add_in_constraint_prepared($field, &$arr_placeholders, $arr_values, $negation = false)
  {
    static $int_index_placeholder = 0;
    $arr_current_placeholders = array();

    if (!is_array($arr_values))
    {
      $int_index_placeholder++;
      $arr_placeholders['in_param_'.$int_index_placeholder] = $value;
      $this->add_in_constraint($field, ':in_param_'.$int_index_placeholder, $negation);
    }
    else
    {
      foreach ($arr_values as $value)
      {
        $int_index_placeholder++;
        $arr_placeholders['in_param_'.$int_index_placeholder] = $value;
        array_push($arr_current_placeholders, ':in_param_'.$int_index_placeholder);
      }
      if (count ($arr_current_placeholders))
      {
        $value = implode(',', $arr_current_placeholders);
        $this->add_in_constraint($field, $value, $negation);
      }
    }
  }

  public function add_multi_constraints($constraints, $constraint_separator)
  {
    foreach($constraints as $constraint)
    {
      list($field,$operator,$value) = $constraint;
      $constraint_strings[] = "$field $operator $value";
    }

    $multi_constraint = '(' . implode(' '.$constraint_separator.' ', $constraint_strings) . ')';

    if($this->or_mode === false)
    {
      $this->constraints[] = $multi_constraint;
    }
    else
    {
      $this->or_constraints[] = $multi_constraint;
    }
  }

  public function start_or_mode()
  {
    $this->or_mode = true;
  }

  public function stop_or_mode()
  {
    $this->constraints[] = '('.implode(' OR ',$this->or_constraints).')';
    $this->or_constraints = array();
    $this->or_mode = false;
  }

  /*
   * Setters for group by
   */
  public function add_groupby($field)
  {
    $field    = is_array($field)     ? implode(',',$field)   : $field;

    $this->groupby[] = $field;
  }

  /*
   * Setters for order by
   */
  public function set_orderby($field, $direction='DESC')
  {
    $this->orderby = array();
    $this->add_orderby($field, $direction);
  }
  public function add_orderby($field, $direction='DESC')
  {
    $this->orderby[] = $field.' '.$direction;
  }

  /*
   * Setters for having
   */
  public function add_having($field, $operator, $value)
  {
    $this->having[] = "$field $operator $value";
  }

  /*
   * Setters for start / limit
   */
  public function set_start($value)
  {
    $this->start = $value;
  }
  public function set_limit($value)
  {
    $this->limit = $value;
  }

  /*
   * Building the blocks
   */
  private function buildSelect()
  {
    $this->selectstring = 'SELECT '. implode(', ',$this->selectfields);
  }
  private function buildTable()
  {
    $this->tablestring = 'FROM '. implode(' ',$this->tables);
  }
  private function buildWhere()
  {
    if (count($this->constraints) > 0)
    {
      $this->wherestring = 'WHERE '. implode(' AND ',$this->constraints);
    }
  }
  private function buildGroupby()
  {
    $this->groupstring = $this->groupby ?
      'GROUP BY '. implode(', ',$this->groupby)
      : '';
  }
  private function buildOrderby()
  {
    $this->orderstring = $this->orderby ?
      'ORDER BY '. implode(', ',$this->orderby)
      : '';
  }
  private function buildHaving()
  {
    $this->havingstring = $this->having ?
      'HAVING '. implode(' AND ',$this->having)
      : '';
  }
  private function buildLimit()
  {
    if ($this->limit > 0)
    {
      $this->limitstring = 'LIMIT '. $this->start .','. $this->limit;
    }
  }

  /*
   * Buildingblock caller
   */
  public function buildQuery()
  {
    $this->buildSelect();
    $this->buildTable();
    $this->buildWhere();
    $this->buildGroupby();
    $this->buildOrderby();
    $this->buildLimit();
    $this->buildHaving();

    $this->sqlstatement = $this->selectstring.
      ' '.$this->tablestring.
      ' '.$this->wherestring.
      ' '.$this->groupstring.
      ' '.$this->havingstring.
      ' '.$this->orderstring.
      ' '.$this->limitstring;
  }
  public function buildCounter()
  {
    // this thing does not work well when grouping (must fix it!!!)
    /* nr of rows exists of all unique 'group by' records
       if those are aliasses, we must extract them from the select-clause
       if there is no group by clause, we must use the select-clause
       except for the sum / count etc statements (and we must
       beware if the 'distinct' clause is used)
     */

    $this->buildSelect();
    $this->buildTable();
    $this->buildWhere();
    $this->buildGroupby();
    $this->buildHaving();

    $this->sqlstatement = 'SELECT count(*) nrresults FROM ('.$this->selectstring.
        ' '.$this->tablestring.
        ' '.$this->wherestring.
        ' '.$this->groupstring.
        ' '.$this->havingstring.') derived';
  }

  /*
   * return my SQL statement
   */
  public function returnSQL()
  {
    $this->buildQuery();
    return $this->sqlstatement;
  }
  public function returnSQLcount()
  {
    $this->buildCounter();
    return $this->sqlstatement;
  }
}
